package com.frame.metadata.wordbook;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import com.frame.database.JBaseService;
import com.frame.database.JServiceFactory;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.database.operate.DataBaseType;
import com.frame.database.util.Seq;
import com.frame.hibernate.HibernateUtil;
import com.frame.util.SelectItem;


public class WordbookService extends JBaseService<WordbookDao>{

	public static WordbookService getIntance(){
		return (WordbookService) JServiceFactory.createJServiceProxy(WordbookService.class);
	}
	public List<JdataBean> loadCatalogs(String parid) throws Exception{
		return getDao().loadCatalogs(parid);
	}
	public List<JdataBean> loadItemTree(String code) throws Exception{
		return getDao().loadItems(code);
	}
	public List<JdataBean> loadItemValTree(String code) throws Exception{
		return getDao().loadItemVals(code);
	}
	public void addCatalog(JdataBean bean) throws Exception{
		bean.put("id", new Seq().getKeyId("Wfwordbook_catalog_id"));
		getDao().getSession().save("Wfwordbook_catalog",bean.getDataMap());
	}
	public JdataBean getCatalog(String id) throws Exception{
		return getDao().getSession().getObject("select * from Wfwordbook_catalog where id=?", new Object[]{id});
	}
	public void updateCatalog(JdataBean bean) throws Exception{
		Map key=new HashMap();
		key.put("id", bean.getString("id"));
		bean.remove("id");
		getDao().getSession().update("Wfwordbook_catalog",bean.getDataMap(),key);
	}
	public void delCatalog(String id) throws Exception{
		getDao().getSession().delete("delete from Wfwordbook_catalog where id=?", new Object[]{id});
	}
	public PageResult queryWordbook(String catalogid,String like,int size,int pageno) throws Exception{
		String sql="select w.*,c.name catalogname from wfwordbook w left join Wfwordbook_catalog c on w.catalogid=c.id where 1=1";
		List parms=new ArrayList();
		if(StringUtils.isNotBlank(catalogid)){
			if(HibernateUtil.getDataBaseType(null).equals(DataBaseType.ORACLE)){
				sql+=" and w.catalogid in (select id from Wfwordbook_catalog connect by prior id =parid start with id="+catalogid+")";
			}else{
				JdataBean d=getDao().getSession().getObject("select getBookCatalogChilds("+catalogid+") ids", null);
				sql+=" and w.catalogid in ("+d.getString("ids")+")";
			}
		}
		if(StringUtils.isNotBlank(like)){
			sql+=" and (lower(w.code) like ? or w.name like ?)";
			parms.add("%"+like.toLowerCase()+"%");
			parms.add("%"+like+"%");
		}
		return getDao().getSession().query2(sql, parms,size,pageno);
	}
	public List<SelectItem> queryItem(String code) throws Exception{
		return getDao().queryItem(code);
	}
	public List<SelectItem> queryItem(String code,String parid) throws Exception{
		String sql="select Item_val value,Item_name text from Wfwordbook_item where lower(code)=?";
		List parms=new ArrayList();
		parms.add(code.toLowerCase());
		if(StringUtils.isNotBlank(parid)){
			sql+=" and parid=?";
			parms.add(parid);
		}
		sql+=" order by orderno,id";
		return getDao().getSession().query2(sql,parms,SelectItem.class);
	}
	public List<SelectItem> queryItemParVal(String code,String parval) throws Exception{
		String sql="select Item_val value,Item_name text from Wfwordbook_item where lower(code)=?";
		List parms=new ArrayList();
		parms.add(code.toLowerCase());
		if(StringUtils.isNotBlank(parval)){
			sql+=" and parid in (select id from Wfwordbook_item where lower(code)=? and item_val=?)";
			parms.add(code.toLowerCase());
			parms.add(parval);
		}else sql+=" and parid is null";
		return getDao().getSession().query2(sql+" order by orderno,id",parms,SelectItem.class);
	}
	public int itemSizeParVal(String code,String parval) throws Exception{
		JdataBean d=getDao().getSession().getObject("select count(id) num from Wfwordbook_item where lower(code)=? and parid in (select id from Wfwordbook_item where lower(code)=? and item_val=?) order by orderno,id",new Object[]{code.toLowerCase(),code.toLowerCase(),parval});
		if(d!=null)return d.getInt("num");
		else return 0;
	}
	public JdataBean queryItemToBean(String code) throws Exception{
		return getDao().queryItemToBean(code);
	}
	public List<JdataBean> queryItembean(String code,String parid) throws Exception{
		String sql="select * from Wfwordbook_item where lower(code)=?";
		if(StringUtils.isBlank(parid))sql+=" and parid is null";
		else sql+=" and parid ="+parid;
		sql+=" order by orderno,id";
		return getDao().getSession().query(sql,new Object[]{code.toLowerCase()});
	}
	public SelectItem getItemName(String code,String val) throws Exception{
		JdataBean d=getDao().getSession().getObject("select Item_val value,Item_name text from Wfwordbook_item where lower(code)=? and Item_val=?", new Object[]{code.toLowerCase(),val});
		if(d!=null)
			return new SelectItem(d.getString("value"),d.getString("text"));
		return null;
	}
	public List<JdataBean> queryItems(String... ids) throws Exception{
		String sql="select * from Wfwordbook_item a";
		if(ids!=null && ids.length>0){
			Object[] objs=new Object[ids.length];
			sql+=" where id in (";
			for(int i=0;i<ids.length;i++){
				if(i!=0)sql+=",";
				sql+="?";
				objs[i]=ids[i];
			}
			sql+=")";
			return getDao().getSession().query(sql,objs);
		}
		return new ArrayList<JdataBean>();
	}

	public void addBook(JdataBean bean) throws Exception{
		bean.put("id", new Seq().getKeyId("wfwordbook_id"));
		getDao().getSession().save("wfwordbook",bean.getDataMap());
	}
	public JdataBean getBook(String id) throws Exception{
		return getDao().getSession().getObject("select * from wfwordbook where id=?", new Object[]{id});
	}
	public void updateBook(JdataBean bean) throws Exception{
		Map key=new HashMap();
		key.put("id", bean.getString("id"));
		bean.remove("id");
		getDao().getSession().update("wfwordbook",bean.getDataMap(),key);
	}
	public void delBook(String id) throws Exception{
		getDao().getSession().delete("delete from wfwordbook where id=?", new Object[]{id});
	}
	public void addItem(JdataBean bean) throws Exception{
		bean.put("id", new Seq().getKeyId("Wfwordbook_item_id"));
		if(StringUtils.isBlank(bean.getString("item_val")))bean.put("item_val", bean.getString("id"));
		getDao().getSession().save("Wfwordbook_item",bean.getDataMap());
	}
	public JdataBean getItem(String id) throws Exception{
		return getDao().getSession().getObject("select * from Wfwordbook_item where id=?", new Object[]{id});
	}

	public void updateItem(JdataBean bean) throws Exception{
		Map key=new HashMap();
		key.put("id", bean.getString("id"));
		bean.remove("id");
		getDao().getSession().update("Wfwordbook_item",bean.getDataMap(),key);
	}
	public void delItem(String id) throws Exception{
		getDao().getSession().delete("delete from Wfwordbook_item where id=?", new Object[]{id});
	}

	public boolean checkCode(String code,String id) throws Exception{
		String sql="select * from Wfwordbook where lower(code)=?";
		if(StringUtils.isNotBlank(id)){
			sql+=" and id<>"+id;
		}
		List<JdataBean> list=getDao().getSession().query(sql, new Object[]{code.toLowerCase()});
		if(list.size()>0)return false;
		else return true;
	}
	public boolean checkItem(String val,String code,String id) throws Exception{
		String sql="select * from Wfwordbook_item where lower(code)=? and Item_val=?";
		if(StringUtils.isNotBlank(id)){
			sql+=" and id<>"+id;
		}
		List<JdataBean> list=getDao().getSession().query(sql, new Object[]{code.toLowerCase(),val});
		if(list.size()>0)return false;
		else return true;
	}
	public void sortItem(String[] ids) throws Exception{
		if(ids!=null){
			int i=0;
			for(String id:ids){
				getDao().getSession().update("update Wfwordbook_item set orderno="+i+" where id=?",new Object[]{id});
				i++;
			}
		}
	}
}
